package DAL;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import entities.Candidate;
import entities.ConnectionManager;
import entities.Voting;

public class VotingDA {
	public Voting[] selectVoting(Voting user)
	{
		boolean whereClause = false;
		String query = "SELECT * from Voting  " ;
		if(user.getAdminID() != -1)
		{
			query += "WHERE AdminID = " + user.getAdminID();
			whereClause = true;
		}
				
		if(user.getVotingID() != -1) 
		{
			if(whereClause)
				query += " AND VotingID = " + user.getVotingID() + "";
			else
				query += " WHERE VotingID = " + user.getVotingID() + "";
			whereClause = true;
		}
		if(user.getVotingGroup() != -1) 
		{
			if(whereClause)
				query += " AND VotingGroup = " + user.getVotingGroup() + "";
			else
				query += " WHERE VotingGroup = " + user.getVotingGroup() + "";
			whereClause = true;
		}
		if(user.getNumberOfVoters() != -1) 
		{
			if(whereClause)
				query += " AND NumberOfVoters = " + user.getNumberOfVoters() + "";
			else
				query += " WHERE NumberOfVoters = " + user.getNumberOfVoters() + "";
			whereClause = true;
		}
		if(!user.getQuestions().equals("")) 
		{
			if(whereClause)
				query += " AND Questions = '" + user.getQuestions() + "'";
			else
				query += " WHERE Questions = '" + user.getQuestions() + "'";
			whereClause = true;
		}
		if(!user.getWhoCanVoteQuery().equals("")) 
		{
			if(whereClause)
				query += " AND WhoCanVoteQuery = '" + user.getWhoCanVoteQuery() + "'";
			else
				query += " WHERE WhoCanVoteQuery = '" + user.getWhoCanVoteQuery() + "'";
			whereClause = true;
		}
		if(!user.getPublicKey().equals("")) 
		{
			if(whereClause)
				query += " AND PublicKey = '" + user.getPublicKey() + "'";
			else
				query += " WHERE PublicKey = '" + user.getPublicKey() + "'";
			whereClause = true;
		}
		if(!user.getPrivateKey().equals("")) 
		{
			if(whereClause)
				query += " AND PrivateKey = '" + user.getPrivateKey() + "'";
			else
				query += " WHERE PrivateKey = '" + user.getPrivateKey() + "'";
			whereClause = true;
		}
		if(!user.getResult().equals("")) 
		{
			if(whereClause)
				query += " AND result = '" + user.getResult() + "'";
			else
				query += " WHERE result = '" + user.getResult() + "'";
			whereClause = true;
		}
		ConnectionManager con = new ConnectionManager();
		ResultSet rs = con.executeSelectQuery(query);
		
		ArrayList<Voting> users = new ArrayList<Voting>();


		try {
			while(rs.next())
			{
				Voting tempUser= new Voting();
				tempUser.setAdminID(rs.getInt("adminID"));
				tempUser.setVotingID(rs.getInt("votingID"));
				tempUser.setVotingGroup(rs.getInt("votingGroup"));
				tempUser.setNumberOfVoters(rs.getInt("numberOfVoters"));
				tempUser.setStartDate(rs.getDate("startDate").getTime());
				tempUser.setEndDate(rs.getDate("endDate").getTime());
				tempUser.setQuestions(rs.getString("questions"));
				tempUser.setWhoCanVoteQuery(rs.getString("whoCanVoteQuery"));
				tempUser.setPublicKey(rs.getString("publicKey"));
				tempUser.setPrivateKey(rs.getString("privateKey"));
				tempUser.setResult(rs.getString("result"));
				users.add(tempUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Voting [] temp = new Voting [0];
		
		return users.toArray(temp);

	}


	public boolean updateVoting(Voting user)
	{
		boolean setClause = false;
		String query = "UPDATE Voting SET " ;
		if(user.getAdminID()!= -1) 
		{
			query += " AdminID = " + user.getAdminID() + "  ";
			setClause = true;
		}
		
		if(!user.getQuestions().equals("")) 
		{
			if(setClause)
				query += " , Questions = '" + user.getQuestions() + "'";
			else
				query += " Questions = '" + user.getQuestions() + "'";
			setClause = true;
		}
		if(user.getEndDate()!= null) 
		{
			if(setClause)
				query += " , EndDate = '" + convertDate(user.getEndDate()) + "'";
			else
				query += " EndDate = '" + convertDate(user.getEndDate()) + "'";
			setClause = true;
		}
		if(user.getStartDate()!= null) 
		{
			if(setClause)
				query += " , StartDate = '" + convertDate(user.getStartDate()) + "'";
			else
				query += " StartDate = '" + convertDate(user.getStartDate()) + "'";
			setClause = true;
		}
	/*	if(!user.getWhoCanVoteQuery().equals("")) 
		{
			if(setClause)
				query += " , WhoCanVoteQuery = '" + user.getWhoCanVoteQuery() + "'";
			else
				query += " WhoCanVoteQuery = '" + user.getWhoCanVoteQuery() + "'";
			setClause = true;
		}*/
		if(!user.getPrivateKey().equals("")) 
		{
			if(setClause)
				query += " , PrivateKey = '" + user.getPrivateKey() + "'";
			else
				query += " PrivateKey = '" + user.getPrivateKey() + "'";
			setClause = true;
		}
		if(!user.getPublicKey().equals("")) 
		{
			if(setClause)
				query += " , PublicKey = '" + user.getPublicKey() + "'";
			else
				query += " PublicKey = '" + user.getPublicKey() + "'";
			setClause = true;
		}
		if(!user.getResult().equals("")) 
		{
			if(setClause)
				query += " , result = '" + user.getResult() + "'";
			else
				query += " result = '" + user.getResult() + "'";
			setClause = true;
		}
		if(user.getNumberOfVoters()!=-1) 
		{
			if(setClause)
				query += " , NumberOfVoters = " + user.getNumberOfVoters() + "";
			else
				query += " NumberOfVoters = " + user.getNumberOfVoters() + "";
			setClause = true;
		}
		if(user.getVotingGroup()!=-1) 
		{
			if(setClause)
				query += " , VotingGroup = " + user.getVotingGroup() + "";
			else
				query += " VotingGroup = " + user.getVotingGroup() + "";
			setClause = true;
		}
		
		query += " WHERE "  
		+ "  AdminID = " + user.getAdminID()
		+ " AND VotingID = " + user.getVotingID();
		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		
		return (x==1);
	}
	
	public boolean insertVoting(Voting user)
	{
		String query = "INSERT INTO Voting (AdminID , Questions , StartDate , EndDate , VotingGroup , numberOfVoters ,whoCanVoteQuery , PublicKey , PrivateKey ) VALUES " +
				"(" +user.getAdminID() +  " , '" + user.getQuestions() + 
				"' , '" + convertDate(user.getStartDate()) + "' , '" + convertDate(user.getEndDate()) +  "' , "+user.getVotingGroup() 
				+ " , " + user.getNumberOfVoters() + " , '" + user.getWhoCanVoteQuery() +  "' , '"+user.getPublicKey() + "' , '"+user.getPrivateKey() + "')";
		
		ConnectionManager con = new ConnectionManager();
		int x = con.executeUpdateQuery(query);
		return x==1;
	}
	public  int insertVotingWithCandidates(Voting user)
	{
		try
		{
		String query = "INSERT INTO Voting (AdminID , Questions , StartDate , EndDate , VotingGroup , numberOfVoters ,whoCanVoteQuery , PublicKey , PrivateKey ) VALUES " +
		"(" +user.getAdminID() +  " , '" + user.getQuestions() + 
		"' , '" + convertDate(user.getStartDate()) + "' , '" + convertDate(user.getEndDate()) +  "' , "+user.getVotingGroup() 
		+ " , " + user.getNumberOfVoters() + " , '" + user.getWhoCanVoteQuery() +  "' , '"+user.getPublicKey() + "' , '"+user.getPrivateKey() + "')";

		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		if(x!=1)
			return -1;
		else
		{
			String iden="select scope_identity() as 'id'";
			ResultSet rs = con.executeSelectQuery(iden);
			rs.next();
			int id=rs.getInt("id");
			return id;
		}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return -1;
		
	}

	
	public int deleteVoting(Voting user)
	{
		boolean whereClause = false;
		String query = "DELETE FROM Voting ";
		if(user.getAdminID() != -1)
		{
			query += "WHERE AdminID = " + user.getAdminID();
			whereClause = true;
		}
				
		if(user.getVotingID() != -1) 
		{
			if(whereClause)
				query += " AND VotingID = " + user.getVotingID() + "";
			else
				query += " WHERE VotingID = " + user.getVotingID() + "";
			whereClause = true;
		}
		if(user.getNumberOfVoters() != -1) 
		{
			if(whereClause)
				query += " AND NumberOfVoters = " + user.getNumberOfVoters() + "";
			else
				query += " WHERE NumberOfVoters = " + user.getNumberOfVoters() + "";
			whereClause = true;
		}
		if(user.getVotingGroup() != -1) 
		{
			if(whereClause)
				query += " AND VotingGroup = " + user.getVotingGroup() + "";
			else
				query += " WHERE VotingGroup = " + user.getVotingGroup() + "";
			whereClause = true;
		}
		if(!user.getQuestions().equals("")) 
		{
			if(whereClause)
				query += " AND Questions = '" + user.getQuestions() + "'";
			else
				query += " WHERE Questions = '" + user.getQuestions() + "'";
			whereClause = true;
		}
		if(!user.getPrivateKey().equals("")) 
		{
			if(whereClause)
				query += " AND PrivateKey = '" + user.getPrivateKey() + "'";
			else
				query += " WHERE PrivateKey = '" + user.getPrivateKey() + "'";
			whereClause = true;
		}
		if(!user.getPublicKey().equals("")) 
		{
			if(whereClause)
				query += " AND PublicKey = '" + user.getPublicKey() + "'";
			else
				query += " WHERE PublicKey = '" + user.getPublicKey() + "'";
			whereClause = true;
		}
		if(!user.getResult().equals("")) 
		{
			if(whereClause)
				query += " AND result = '" + user.getResult() + "'";
			else
				query += " WHERE result = '" + user.getResult() + "'";
			whereClause = true;
		}
		if(!user.getWhoCanVoteQuery().equals("")) 
		{
			if(whereClause)
				query += " AND WhoCanVoteQuery = '" + user.getWhoCanVoteQuery() + "'";
			else
				query += " WHERE WhoCanVoteQuery = '" + user.getWhoCanVoteQuery() + "'";
			whereClause = true;
		}
		
		ConnectionManager con = new ConnectionManager();
		
		int x = con.executeUpdateQuery(query);
		
		return x;
	}
	
	private String convertDate (java.sql.Date x )
	{
		return x.toString();
	}
	
	public static void main(String[] args) {
		Voting u = new Voting();

		//u.setAdminID(2);
		//u.setVotingID(3);
		//u.setEndDate(2000 , 11 , 22);
		//u.setStartDate(2010 , 2 , 24);
		//u.setQuestions("a");
		//u.setNumberOfVoters(11);
		//u.setVotingGroup(5);
		//u.setWhoCanVoteQuery("a");
		//u.setPrivateKey("as");
		u.setPublicKey("as2");
		VotingDA da = new VotingDA();
		
		System.out.println(da.selectVoting(u).length);
	}
}
